package edu.flying.panda.taskmanager;

import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


/**
 * 
 * @author Flying Panda
 *Handel SQLite functions
 *
 */
public class DatabaseHandler extends SQLiteOpenHelper {
	// current user id
	private static int CURRENT_USER_ID;
	// db version
	private static final int DATABASE_VERSION = 1;
	// db name
	private static final String DATABASE_NAME = "dbManager";
	// table name
	private static final String TABLE_USERS = "users";
	private static final String TABLE_TASKS = "tasks";
	// user table column names
	private static final String USER_KEY_ID = "_id";
	private static final String USER_KEY_USERNAME = "username";
	private static final String USER_KEY_PASSWORD = "password";
	private static final String USER_KEY_NAME = "name";
	private static final String USER_KEY_EMAIL = "email";
	// task table column names
	private static final String TASK_KEY_ID = "_id";
	private static final String TASK_KEY_USERID = "user_id";
	private static final String TASK_KEY_DESCRIPTION = "description";
	private static final String TASK_KEY_LOCATION = "location";
	private static final String TASK_KEY_DATE = "date";
	private static final String TASK_KEY_DETAIL = "detail";
	private static final String TASK_KEY_TYPE = "type";
	private static final String TASK_KEY_COMPLETED = "completed";
	
	
	/**
	 * Constructor method for the database with tables
	 */
	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_USERS_TABLE = 
				String.format("CREATE TABLE %s (%s INTEGER PRIMARY KEY,	%s TEXT, %s TEXT, %s TEXT, %s TEXT);",
						TABLE_USERS, USER_KEY_ID, USER_KEY_USERNAME, USER_KEY_PASSWORD,
						USER_KEY_NAME, USER_KEY_EMAIL);
		String CREATE_TASKS_TABLE =
				String.format("CREATE TABLE %s (%s INTEGER PRIMARY KEY,	%s INTEGER, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s INTEGER);",
						TABLE_TASKS, TASK_KEY_ID, TASK_KEY_USERID, TASK_KEY_DESCRIPTION, TASK_KEY_LOCATION, TASK_KEY_DATE, TASK_KEY_DETAIL, TASK_KEY_TYPE, TASK_KEY_COMPLETED);
		
		db.execSQL(CREATE_TASKS_TABLE);
		db.execSQL(CREATE_USERS_TABLE);
		
	}
	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASKS);
		// Create tables again
		onCreate(db);
	}
	
	/**
	 * Adds a User to the table
	 * @param user The User to be added to the table
	 */
	public void addUser(User user) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(USER_KEY_USERNAME, user.getUsername());
		values.put(USER_KEY_PASSWORD, user.getPassword());
		values.put(USER_KEY_NAME, user.getName());
		values.put(USER_KEY_EMAIL, user.getEmail());

		// inserting row
		db.insert(TABLE_USERS, null, values);
		db.close();
	}

	
	/**
	 * Getter method for a User (int ID as param)
	 * @param id The ID of the User to be retrieved
	 */
	public User getUser(int id) {

		SQLiteDatabase db = this.getReadableDatabase();

		// cursor returned will only be one row of the table, with the matching id
		Cursor cursor = db.query(TABLE_USERS, new String[] { USER_KEY_ID,
				USER_KEY_USERNAME, USER_KEY_PASSWORD, USER_KEY_NAME, USER_KEY_EMAIL }, USER_KEY_ID
				+ "=?", new String[] { String.valueOf(id) }, null, null, null, null);
	  
		if (cursor != null)
				if (cursor.moveToFirst()){
					User user = new User((cursor.getInt(0)),cursor.getString(1), cursor.getString(2),
						cursor.getString(3), cursor.getString(4));
			
					return user;
		}
		return null;
	}
	
	
	/**
	 * Deletes a Task from the table (ID as input)
	 * @param id The ID of the task to be deleted
	 */
	public User getUser(String username) {
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		// cursor returned will only be one row of the table, with the matching
		// id
		Cursor cursor = db.query(TABLE_USERS, new String[] { USER_KEY_ID,
				USER_KEY_USERNAME, USER_KEY_PASSWORD, USER_KEY_NAME, USER_KEY_EMAIL }, USER_KEY_USERNAME
				+ "=?", new String[] { username }, null, null, null, null);
		

		if (cursor != null)
			if (cursor.moveToFirst()){
				User user = new User(Integer.parseInt(cursor.getString(0)),
						cursor.getString(1), cursor.getString(2),
						cursor.getString(3), cursor.getString(4));
				return user;
			}
		return null;
	}
	
	/**
	 * Getter for the ArrayList of ALL Users in the table
	 * @return Returns all users in an ArrayList<User> 
	 */
	public ArrayList<User> getAllUsers() {
		ArrayList<User> userList = new ArrayList<User>();

		// select all query
		String selectQuery = "SELECT * FROM " + TABLE_USERS;

		SQLiteDatabase db = this.getReadableDatabase();
		// just a cursor that points to the user table TABLE_USERS
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		// adding users to list
		if (cursor.moveToFirst()) {
			do {
				User user = new User();
				user.setId(Integer.parseInt(cursor.getString(0)));
				user.setUsername(cursor.getString(1));
				user.setPassword(cursor.getString(2));
				user.setName(cursor.getString(3));
				user.setEmail(cursor.getString(4));

				userList.add(user);
			} while (cursor.moveToNext());
		}

		return userList;
	}
	
	/**
	 * Deletes a User from the database (User as param)
	 * @param user The User variable to be removed
	 */
	public void deleteUser(User user) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_USERS, USER_KEY_ID + " = ?",
				new String[] { String.valueOf(user.getId()) });
		db.close();
	}
	
	
	/**
	 * Deletes a User from the database (Task ID as param)
	 * @param id The User ID to be removed
	 */
	public void deleteUser(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_USERS, USER_KEY_ID + " = ?",
				new String[] { String.valueOf(id) });
		db.close();
	}
	
	/**
	 * Updates a single user's info
	 * @param user The User variable to be updated
	 * @return int The number of rows affected (should be 1)
	 */
	public int updateUser(User user) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put(USER_KEY_ID, user.getId());
		values.put(USER_KEY_USERNAME, user.getUsername());
		values.put(USER_KEY_PASSWORD, user.getPassword());
		values.put(USER_KEY_NAME, user.getName());
		values.put(USER_KEY_EMAIL, user.getEmail());

		// update row
		return db.update(TABLE_USERS, values, USER_KEY_ID + " = ?",
				new String[] { String.valueOf(user.getId()) });
	}
	
	/**
	 * Getter method for current User ID
	 * @return int Returns the current User ID
	 */
	public int getCURRENT_USERID() {
		return CURRENT_USER_ID;
	}
	
	/**
	 * Setter method for current User ID
	 * @param currentUserId The integer of the user ID to set
	 */
	public void setCURRENT_USERID(int currentUserId) {
		CURRENT_USER_ID = currentUserId;
	}
	
	/**
	 * Adds a new row to the table containing Task values
	 * @param task Adds the values of this Task object
	 */
	public void addTask(Task task){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		
		values.put(TASK_KEY_USERID, getCURRENT_USERID());
		values.put(TASK_KEY_DESCRIPTION, task.getDescription() );
		values.put(TASK_KEY_LOCATION, task.getLocation() );
		Log.d("dbh","7");
		values.put(TASK_KEY_DATE, task.getDueDate().toString());
		Log.d("dbh","8");
		values.put(TASK_KEY_DETAIL, task.getDetailedDescription() );
		values.put(TASK_KEY_TYPE, task.getType() );
		values.put(TASK_KEY_COMPLETED,  task.isCompleted());
		
		db.insert(TABLE_TASKS, null, values);
		db.close();		
	}
	
	/**
	 * Getter for a Task from the SQL database (int ID as param)
	 * @param id The ID of the Task to be retrieved
	 * @return task Returns the task which matches the task ID entered
	 */
	public Task getTask(int id){
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.query(TABLE_TASKS, new String[] {TASK_KEY_ID, TASK_KEY_USERID, TASK_KEY_DESCRIPTION,
				TASK_KEY_LOCATION, TASK_KEY_DATE, TASK_KEY_DETAIL, TASK_KEY_TYPE, TASK_KEY_COMPLETED}, 
				TASK_KEY_ID + "=?",	new String[] {String.valueOf(id)}, null, null, null, null);
		
		if (cursor != null)
				cursor.moveToFirst();	
			
		Task task = new Task(cursor.getInt(0), cursor.getInt(1), cursor.getString(2), cursor.getString(3), 
				new Date(cursor.getString(4)),cursor.getString(5), cursor.getString(6), (cursor.getInt(7) == 1)? true:false );
		
		return task;
	}
	
	/**
	 * Getter for a Task from the SQL database (Task Name as param)
	 * @param description The String of the desription(Task Name) to retrieve
	 * @return Task 
	 */
	public Task getTask(String description){
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.query(TABLE_TASKS, new String[] {TASK_KEY_ID, TASK_KEY_USERID, TASK_KEY_DESCRIPTION,
				TASK_KEY_LOCATION, TASK_KEY_DATE, TASK_KEY_DETAIL, TASK_KEY_TYPE, TASK_KEY_COMPLETED}, 
				TASK_KEY_DESCRIPTION + "=?", new String[] {description}, null, null, null, null);
		
		if (cursor != null)
				cursor.moveToFirst();	
			
		Task task = new Task(cursor.getInt(0), cursor.getInt(1), cursor.getString(2), cursor.getString(3), 
				new Date(cursor.getString(4)),cursor.getString(5), cursor.getString(6), (cursor.getInt(7) == 1)? true:false );
		
		return task;
	}
	
	//fix: to find by user id 	
	/**
	 * Getter method for the complete ArrayList of Tasks
	 * @return ArrayList<Task> Returns the entire table as an ArrayList of Task objects
	 */
	public ArrayList<Task> getAllTasks(){
		
		ArrayList<Task> taskList = new ArrayList<Task>();
		
		 // Select All Query
	    String selectQuery = "SELECT  * FROM " + TABLE_TASKS;
	    
	    SQLiteDatabase db = this.getReadableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null); 
		 
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            Task task = new Task();
	            
	            task.setId(cursor.getInt(0));
	            task.setUserId(cursor.getInt(1));
	            task.setDescription(cursor.getString(2));
	            task.setLocation(cursor.getString(3));
	            task.setDueDate(new Date(cursor.getString(4)));
	            task.setDetailedDescription(cursor.getString(5));
	            task.setType(cursor.getString(6));
	            task.setCompleted((cursor.getInt(7) == 1)? true:false);
	            
	            // Adding contact to list
	            taskList.add(task);
	        } while (cursor.moveToNext());
	    }
		return taskList;
	}
	
	/**
	 * Getter for the Tasks of a specific type
	 * @param type The type/category of Task to search for/return
	 * @return Returns an ArrayList of Tasks which match the param type
	 */
	public ArrayList<Task> getAllTasks(String type){
		ArrayList<Task> taskList = new ArrayList<Task>();
		SQLiteDatabase db = this.getReadableDatabase();
		
		// Select All Query
	    String selectQuery = "SELECT * FROM " + TABLE_TASKS + " WHERE " + TASK_KEY_TYPE + "=" + "'"+type+"'" ;
	    
	    Cursor cursor = db.rawQuery(selectQuery, null); 
	    
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            Task task = new Task();
	            
	            task.setId(cursor.getInt(0));
	            task.setUserId(cursor.getInt(1));
	            task.setDescription(cursor.getString(2));
	            task.setLocation(cursor.getString(3));
	            Log.d("dh", "1");
	            task.setDueDate(new Date(cursor.getString(4)));
	            Log.d("dh", "2");
	            task.setDetailedDescription(cursor.getString(5));
	            task.setType(cursor.getString(6));
	            task.setCompleted((cursor.getInt(7) == 1)? true:false);
	            
	            // Adding contact to list
	            taskList.add(task);
	        } while (cursor.moveToNext());
	    }
		return taskList;
	}
	
	
	
	/**
	 * Getter method for all Completed Tasks
	 * @return Returns an ArrayList<Task> of the completed Tasks
	 */
	public ArrayList<Task> getAllCompletedTasks(){
		ArrayList<Task> taskList = new ArrayList<Task>();
		SQLiteDatabase db = this.getReadableDatabase();
		
		// Select All Query
	    String selectQuery = "SELECT * FROM " + TABLE_TASKS + " WHERE " + TASK_KEY_COMPLETED + "=" + 0 ;
	    
	    Cursor cursor = db.rawQuery(selectQuery, null); 
	    
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            Task task = new Task();
	            
	            task.setId(cursor.getInt(0));
	            task.setUserId(cursor.getInt(1));
	            task.setDescription(cursor.getString(2));
	            task.setLocation(cursor.getString(3));
	            task.setDueDate(new Date(cursor.getString(4)));
	            task.setDetailedDescription(cursor.getString(5));
	            task.setType(cursor.getString(6));
	            task.setCompleted((cursor.getInt(7) == 1)? true:false);
	            
	            // Adding contact to list
	            taskList.add(task);
	        } while (cursor.moveToNext());
	    }
		return taskList;
	}
	
	/**
	 * Getter method for all Tasks before a certain date
	 * @return Returns an ArrayList<Task> of the Tasks before param date
	 */
	public ArrayList<Task> getBefore(Date date){
		
		ArrayList<Task> taskList = getAllTasks();
		

		Log.d("date:", date.toString());
		
		for( int iCounter = 0 ; iCounter < taskList.size();){
			//if current task's due date is NOT before parameter date
			Log.d("dueDate",  taskList.get(iCounter).getDueDate().toString());
			if ( taskList.get(iCounter).getDueDate().compareTo(date) != -1) {
				Log.d("!=-1",  taskList.get(iCounter).toString());
				taskList.remove(iCounter);
			}else {
				iCounter++;
			}	
			
		}
		
		return taskList;
		
	}
	/**
	 * Getter method for all Tasks after a certain date
	 * @return Returns an ArrayList<Task> of the Tasks after param date
	 */
	public ArrayList<Task> getAfter(Date date){
		
		ArrayList<Task> taskList = getAllTasks();
		
		Log.d("date:", date.toString());
		
		for( int iCounter = 0 ; iCounter < taskList.size();){
			//if current task's due date is NOT after parameter date
			Log.d("dueDate",  taskList.get(iCounter).getDueDate().toString());
			if ( taskList.get(iCounter).getDueDate().compareTo(date) != 1) {
				Log.d("!=1",  taskList.get(iCounter).toString());
				taskList.remove(iCounter);
			}else {
				iCounter++;
			}	
			
		}
		
		return taskList;
		
	}

	/**
	 * Deletes a task from the table (Task as param)
	 * @param task The Task object to delete from the table
	 */
	public void deleteTask(Task task){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_TASKS, TASK_KEY_ID + " = ?",
				new String[] { String.valueOf(task.getId()) });
		db.close();
	}
	
	/**
	 * Deletes a Task from the table (ID as param)
	 * @param id The ID of the task to be deleted
	 */
	public void deleteTask(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_TASKS, TASK_KEY_ID + " = ?",
				new String[] { String.valueOf(id) });
		db.close();
	}
	
	/**
	 * Updates a Task (Task as param)
	 * @param task The Task object to be updated in the table
	 */
	public int updateTask(Task task){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		
		values.put(TASK_KEY_USERID, getCURRENT_USERID());
		values.put(TASK_KEY_DESCRIPTION, task.getDescription() );
		values.put(TASK_KEY_LOCATION, task.getLocation() );
		values.put(TASK_KEY_DATE, task.getDueDate().toString());
		values.put(TASK_KEY_DETAIL, task.getDetailedDescription() );
		values.put(TASK_KEY_TYPE, task.getType() );
		values.put(TASK_KEY_COMPLETED,  task.isCompleted());
		
		return db.update(TABLE_TASKS, values, TASK_KEY_ID + " = ?",
				new String[] { String.valueOf(task.getId()) });
	}

}